import os
import sqlite3
from dbutils.pooled_db import PooledDB
import threading
class SqliteHelper(object):
    _lock = threading.Lock()
    
    def __new__(cls, *args, **kwargs):
        """单例模式"""
        if hasattr(cls, 'instance'):
            return cls.instance
        
        with cls._lock:
            if not hasattr(cls, 'instance'):
                mincached = 1
                maxcached = 2
                maxconnections = 10
                blocking = True
                database = 'knowledge'
                cls._pool = PooledDB(creator=sqlite3,
                                        mincached=mincached,
                                        maxcached=maxcached,
                                        maxconnections=maxconnections,
                                        blocking=blocking,
                                        check_same_thread=False,
                                        database=database
                                        )
                cls.instance = super(SqliteHelper, cls).__new__(cls)
            return cls.instance

    def __init__(self, *args, **kwargs):
        pass

    def selectall(self, sql, values=()):
        connection = self._pool.connection()
        cursor = connection.cursor()
        try:
            cursor.execute(sql, values)
            return cursor.fetchall()
        finally:
            connection.close()
            cursor.close()

    def selectone(self, sql, values=()):
        connection = self._pool.connection()
        cursor = connection.cursor()
        try:
            cursor.execute(sql, values)
            return cursor.fetchone()
        finally:
            connection.close()
            cursor.close()

    def insert(self, sql, values=()):
        connection = self._pool.connection()
        cursor = connection.cursor()
        try:
            cursor.execute(sql, values)
            lastid = cursor.lastrowid
            connection.commit()
            return lastid
        except Exception as e:
            raise e
        finally:
            connection.close()
            cursor.close()

    def insertmany(self, sql, values=()):
        connection = self._pool.connection()
        cursor = connection.cursor()
        try:
            cursor.executemany(sql, values)
            rowcount = cursor.rowcount
            connection.commit()
            return rowcount
        except Exception as e:
            raise e
        finally:
            connection.close()
            cursor.close()

    def deletemany(self, sql, values=()):
        connection = self._pool.connection()
        cursor = connection.cursor()
        try:
            cursor.executemany(sql, values)
            rowcount = cursor.rowcount
            connection.commit()
            return rowcount
        except Exception as e:
            raise e
        finally:
            connection.close()
            cursor.close()

    def updatemany(self, sql, values=()):
        connection = self._pool.connection()
        cursor = connection.cursor()
        try:
            cursor.executemany(sql, values)
            rowcount = cursor.rowcount
            connection.commit()
            return rowcount
        except Exception as e:
            raise e
        finally:
            connection.close()
            cursor.close()

    def update(self, sql, values=()):
        return self.execute(sql, values)

    def delete(self, sql, values=()):
        return self.execute(sql, values)

    def execute(self, sql, values=()):
        connection = self._pool.connection()
        cursor = connection.cursor()
        try:
            cursor.execute(sql, values)
            rowcount = cursor.rowcount
            connection.commit()
            return rowcount
        except Exception as e:
            raise e
        finally:
            connection.close()
            cursor.close()

    def transac_execute(self, items):
        connection = self._pool.connection()
        connection.begin()
        cursor = connection.cursor()
        try:
            rowcount = 0
            for item in items:
                sql = item[0]
                values = item[1]
                cursor.execute(sql, values)
                rowcount += cursor.rowcount
            connection.commit()
            return rowcount
        except Exception as e:
            connection.rollback()
            raise e
        finally:
            connection.close()
            cursor.close()

class SqliteDB(SqliteHelper):
    def __init__(self, *args, **kwargs):
        super(SqliteDB, self).__init__(*args, **kwargs)